﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlServerCe;
using System.Text;
using Entities;

namespace Distribution.Data
{
    public class DTransformer
    {
        #region - Public methods -

        public static DataTable GetTransformerDetails(EPole pole)
        {
            DataTable _Dt = new DataTable();
            SqlCeConnection _Con = null;
            SqlCeDataAdapter _Da;
            StringBuilder query = new StringBuilder();

            try
            {
                _Con = Helper.GetConnection();

                query.Append("SELECT tb_Transformers.TransformerId, tb_Transformers.SerialNumber, tb_Transformers.Comments ");
                query.Append("FROM tb_Transformers ");
                query.Append("WHERE PoleFk = @PoleId ");
                query.Append("ORDER BY tb_Transformers.TransformerId");

                _Da = new SqlCeDataAdapter(query.ToString(), _Con);
                _Da.SelectCommand.CommandType = CommandType.Text;
                _Da.SelectCommand.Parameters.Add(Helper.CreateIntParameter("@PoleId", ParameterDirection.Input, false, pole.Id));

                _Da.Fill(_Dt);
                return _Dt;
            }
            finally
            {
                if (_Con != null)
                {
                    _Con.Close();
                }
            }
        }

        public static ETransformer GetById(int id)
        {
            SqlCeConnection con = null;
            StringBuilder query = new StringBuilder();

            try
            {
                con = Helper.GetConnection();

                query.Append("SELECT TransformerId, Latitude, Longitude, SerialNumber, CreatedDate, Comments, TransformerType, Type, Class, Phase, UtilityNumber, Manufacturer, [Primary], Secondary, Arrestor, DualRated, Pre1978, PCBType, PPM, ArrestorKv, CutOut, CutOutKv, PoleNumber, Can, DeterminedBy, TestNumber, Impeddance, Taps, Polarity, ManufDate, Sticker ");
                query.Append("FROM tb_Transformers ");
                query.Append("WHERE TransformerId = @TransformerId ");

                SqlCeCommand command = new SqlCeCommand(query.ToString(), con);
                command.CommandType = CommandType.Text;
                command.Parameters.Add(Helper.CreateIntParameter("@TransformerId", ParameterDirection.Input, false, id));

                SqlCeDataReader dr = command.ExecuteReader();
                ETransformer loadedTransformer = new ETransformer();

                if (dr.Read())
                {
                    loadedTransformer.Id = dr.GetInt32(dr.GetOrdinal("TransformerId"));

                    if (!dr.IsDBNull(dr.GetOrdinal("Latitude")))
                        loadedTransformer.Latitude = dr.GetDouble(dr.GetOrdinal("Latitude"));

                    if (!dr.IsDBNull(dr.GetOrdinal("Longitude")))
                        loadedTransformer.Longitude = dr.GetDouble(dr.GetOrdinal("Longitude"));

                    if (!dr.IsDBNull(dr.GetOrdinal("SerialNumber")))
                        loadedTransformer.SerialNumber = dr.GetString(dr.GetOrdinal("SerialNumber"));

                    if (!dr.IsDBNull(dr.GetOrdinal("CreatedDate")))
                        loadedTransformer.CreatedDate = dr.GetDateTime(dr.GetOrdinal("CreatedDate"));

                    if (!dr.IsDBNull(dr.GetOrdinal("Comments")))
                        loadedTransformer.Comments = dr.GetString(dr.GetOrdinal("Comments"));

                    if (!dr.IsDBNull(dr.GetOrdinal("TransformerType")))
                        loadedTransformer.TransformerType = DTransformerLookup.GetByIdType(dr.GetInt32(dr.GetOrdinal("TransformerType")), TransformerLookUpType.TransformerType);

                    if (!dr.IsDBNull(dr.GetOrdinal("Class")))
                        loadedTransformer.Class = DTransformerLookup.GetByIdType(dr.GetInt32(dr.GetOrdinal("Class")), TransformerLookUpType.Class);

                    if (!dr.IsDBNull(dr.GetOrdinal("Phase")))
                        loadedTransformer.Phase = DTransformerLookup.GetByIdType(dr.GetInt32(dr.GetOrdinal("Phase")), TransformerLookUpType.Phase);

                    if (!dr.IsDBNull(dr.GetOrdinal("Type")))
                        loadedTransformer.Type = DTransformerLookup.GetByIdType(dr.GetInt32(dr.GetOrdinal("Type")), TransformerLookUpType.Type);

                    if (!dr.IsDBNull(dr.GetOrdinal("PCBType")))
                        loadedTransformer.PCBType = DTransformerLookup.GetByIdType(dr.GetInt32(dr.GetOrdinal("PCBType")), TransformerLookUpType.PCBType);

                    if (!dr.IsDBNull(dr.GetOrdinal("UtilityNumber")))
                        loadedTransformer.UtilityNumber = dr.GetString(dr.GetOrdinal("UtilityNumber"));

                    if (!dr.IsDBNull(dr.GetOrdinal("Manufacturer")))
                        loadedTransformer.Manufacturer = dr.GetString(dr.GetOrdinal("Manufacturer"));

                    if (!dr.IsDBNull(dr.GetOrdinal("Primary")))
                        loadedTransformer.Primary = dr.GetString(dr.GetOrdinal("Primary"));

                    if (!dr.IsDBNull(dr.GetOrdinal("Secondary")))
                        loadedTransformer.Secondary = dr.GetString(dr.GetOrdinal("Secondary"));

                    if (!dr.IsDBNull(dr.GetOrdinal("Arrestor")))
                        loadedTransformer.Arrestor = dr.GetBoolean(dr.GetOrdinal("Arrestor"));

                    if (!dr.IsDBNull(dr.GetOrdinal("DualRated")))
                        loadedTransformer.DualRated = dr.GetBoolean(dr.GetOrdinal("DualRated"));

                    if (!dr.IsDBNull(dr.GetOrdinal("Pre1978")))
                        loadedTransformer.Pre1978 = dr.GetBoolean(dr.GetOrdinal("Pre1978"));

                    if (!dr.IsDBNull(dr.GetOrdinal("PPM")))
                        loadedTransformer.PPM = dr.GetString(dr.GetOrdinal("PPM"));

                    if (!dr.IsDBNull(dr.GetOrdinal("CutOut")))
                        loadedTransformer.Cutout = dr.GetBoolean(dr.GetOrdinal("CutOut"));

                    if (!dr.IsDBNull(dr.GetOrdinal("PoleNumber")))
                        loadedTransformer.PoleNumber = dr.GetString(dr.GetOrdinal("PoleNumber"));

                    if (!dr.IsDBNull(dr.GetOrdinal("ArrestorKv")))
                        loadedTransformer.ArrestorKv = DTransformerLookup.GetByIdType(dr.GetInt32(dr.GetOrdinal("ArrestorKv")), TransformerLookUpType.ArrestorKv);

                    if (!dr.IsDBNull(dr.GetOrdinal("CutOutKv")))
                        loadedTransformer.CutoutKv = DTransformerLookup.GetByIdType(dr.GetInt32(dr.GetOrdinal("CutOutKv")), TransformerLookUpType.CutoutKv);

                    if (!dr.IsDBNull(dr.GetOrdinal("Can")))
                        loadedTransformer.Can = DTransformerLookup.GetByIdType(dr.GetInt32(dr.GetOrdinal("Can")), TransformerLookUpType.Can);

                    if (!dr.IsDBNull(dr.GetOrdinal("DeterminedBy")))
                        loadedTransformer.DeterminedBy = DTransformerLookup.GetByIdType(dr.GetInt32(dr.GetOrdinal("DeterminedBy")), TransformerLookUpType.DeterminedBy);

                    if (!dr.IsDBNull(dr.GetOrdinal("TestNumber")))
                        loadedTransformer.TestNumber = dr.GetString(dr.GetOrdinal("TestNumber"));

                    if (!dr.IsDBNull(dr.GetOrdinal("Impeddance")))
                        loadedTransformer.Impeddance = dr.GetString(dr.GetOrdinal("Impeddance"));

                    if (!dr.IsDBNull(dr.GetOrdinal("Taps")))
                        loadedTransformer.Taps = dr.GetString(dr.GetOrdinal("Taps"));

                    if (!dr.IsDBNull(dr.GetOrdinal("Polarity")))
                        loadedTransformer.Polarity = dr.GetString(dr.GetOrdinal("Polarity"));

                    if (!dr.IsDBNull(dr.GetOrdinal("Sticker")))
                        loadedTransformer.Sticker = dr.GetBoolean(dr.GetOrdinal("Sticker"));

                    if (!dr.IsDBNull(dr.GetOrdinal("ManufDate")))
                        loadedTransformer.ManufDate = dr.GetDateTime(dr.GetOrdinal("ManufDate"));

                    //Load potransformerle's notes
                    query = new StringBuilder("SELECT idKeyword ");
                    query.Append("FROM tb_Transformers_Keywords ");
                    query.Append("WHERE idTransformer = @idTransformer ");
                    command = new SqlCeCommand(query.ToString(), con);
                    command.Parameters.Add(Helper.CreateIntParameter("@idTransformer", ParameterDirection.Input, false, loadedTransformer.Id));

                    loadedTransformer.Notes = new List<ETransformerLookUp>();
                    SqlCeDataReader drNotes = command.ExecuteReader();

                    while (drNotes.Read())
                    {
                        ETransformerLookUp note = DTransformerLookup.GetByIdType(drNotes.GetInt32(drNotes.GetOrdinal("idKeyword")), TransformerLookUpType.Keywords);
                        loadedTransformer.Notes.Add(note);
                    }
                    
                    //Load transformer's images
                    query = new StringBuilder();
                    query.Append("SELECT idTransformerImage, FileName ");
                    query.Append("FROM tb_TransformerImages ");
                    query.Append("WHERE idTransformer = @idTransformer ");
                    command = new SqlCeCommand(query.ToString(), con);
                    command.Parameters.Add(Helper.CreateIntParameter("@idTransformer", ParameterDirection.Input, false, loadedTransformer.Id));

                    loadedTransformer.Images = new List<EImage>();
                    SqlCeDataReader drImages = command.ExecuteReader();

                    while (drImages.Read())
                    {
                        EImage image = new EImage();

                        image.ImageType = ImageType.Transformer;
                        image.Id = drImages.GetInt32(drImages.GetOrdinal("idTransformerImage"));
                        image.FileName = drImages.GetString(drImages.GetOrdinal("FileName"));

                        loadedTransformer.Images.Add(image);
                    }


                }

                return loadedTransformer;
            }
            finally
            {
                if (con != null)
                    con.Close();
            }
        }

        public static ETransformer Save(ETransformer transformer)
        {
            SqlCeTransaction tx = null;
            SqlCeConnection con = null;
            StringBuilder query = new StringBuilder();

            try
            {
                con = Helper.GetConnection();
                tx = con.BeginTransaction();

                #region - Pole INSERT -

                query.AppendLine("INSERT INTO tb_Transformers (Latitude, Longitude, PoleFk, SerialNumber, CreatedDate, Comments, TransformerType, Type, Class, Phase, UtilityNumber, Manufacturer, [Primary], Secondary, Arrestor, DualRated, Pre1978, PCBType, PPM, ArrestorKv, CutOut, CutOutKv, PoleNumber, Can, DeterminedBy, TestNumber, Impeddance, Taps, Polarity, ManufDate, Sticker) VALUES (");
                query.AppendLine("@Latitude,");
                query.AppendLine("@Longitude,");
                query.AppendLine("@PoleFk,");
                query.AppendLine("@Number,");
                query.AppendLine("@CreatedDate,");
                query.AppendLine("@Comments,");

                query.AppendLine("@TransformerType,");
                query.AppendLine("@Type,");
                query.AppendLine("@Class,");
                query.AppendLine("@Phase,");
                query.AppendLine("@UtilityNumber,");
                query.AppendLine("@Manufacturer,");
                query.AppendLine("@Primary,");
                query.AppendLine("@Secondary,");
                query.AppendLine("@Arrestor,");
                query.AppendLine("@DualRated,");
                query.AppendLine("@Pre1978,");
                query.AppendLine("@PCBType,");
                query.AppendLine("@PPM,");
                query.AppendLine("@ArrestorKv,");
                query.AppendLine("@CutOut,");
                query.AppendLine("@CutOutKv,");
                query.AppendLine("@PoleNumber,");
                query.AppendLine("@Can,");
                query.AppendLine("@DeterminedBy,");
                query.AppendLine("@TestNumber,");
                query.AppendLine("@Impeddance,");
                query.AppendLine("@Taps,");
                query.AppendLine("@Polarity,");
                query.AppendLine("@ManufDate,");
                query.AppendLine("@Sticker)");

                SqlCeCommand command = new SqlCeCommand(query.ToString(), con, tx);
                command.CommandType = CommandType.Text;

                if (transformer.Latitude != Double.MinValue)
                    command.Parameters.Add(Helper.CreateDecimalParameter("@Latitude", ParameterDirection.Input, false, 19, 6, transformer.Latitude));
                else
                    command.Parameters.Add(Helper.CreateDecimalParameter("@Latitude", ParameterDirection.Input, false, 19, 6, DBNull.Value));

                if (transformer.Longitude != Double.MinValue)
                    command.Parameters.Add(Helper.CreateDecimalParameter("@Longitude", ParameterDirection.Input, false, 19, 6, transformer.Longitude));
                else
                    command.Parameters.Add(Helper.CreateDecimalParameter("@Longitude", ParameterDirection.Input, false, 19, 6, DBNull.Value));

                command.Parameters.Add(Helper.CreateIntParameter("@PoleFk", ParameterDirection.Input, false, transformer.Pole.Id));

                if (transformer.SerialNumber != string.Empty)
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@Number", 25, ParameterDirection.Input, false, transformer.SerialNumber));
                else
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@Number", 25, ParameterDirection.Input, false, DBNull.Value));

                if (transformer.CreatedDate != DateTime.MinValue)
                    command.Parameters.Add(Helper.CreateDateTimeParameter("@CreatedDate", ParameterDirection.Input, false, transformer.CreatedDate));
                else
                    command.Parameters.Add(Helper.CreateDateTimeParameter("@CreatedDate", ParameterDirection.Input, false, DBNull.Value));

                if (transformer.Comments != string.Empty)
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@Comments", 512, ParameterDirection.Input, false, transformer.Comments));
                else
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@Comments", 512, ParameterDirection.Input, false, DBNull.Value));
                
                if (transformer.TransformerType != null && transformer.TransformerType.Id > 0)
                    command.Parameters.Add(Helper.CreateIntParameter("@TransformerType", ParameterDirection.Input, false, transformer.TransformerType.Id));
                else
                    command.Parameters.Add(Helper.CreateIntParameter("@TransformerType", ParameterDirection.Input, false, DBNull.Value));

                if (transformer.Type != null && transformer.Type.Id > 0)
                    command.Parameters.Add(Helper.CreateIntParameter("@Type", ParameterDirection.Input, false, transformer.Type.Id));
                else
                    command.Parameters.Add(Helper.CreateIntParameter("@Type", ParameterDirection.Input, false, DBNull.Value));

                if (transformer.Class != null && transformer.Class.Id > 0)
                    command.Parameters.Add(Helper.CreateIntParameter("@Class", ParameterDirection.Input, false, transformer.Class.Id));
                else
                    command.Parameters.Add(Helper.CreateIntParameter("@Class", ParameterDirection.Input, false, DBNull.Value));

                if (transformer.Phase != null && transformer.Phase.Id > 0)
                    command.Parameters.Add(Helper.CreateIntParameter("@Phase", ParameterDirection.Input, false, transformer.Phase.Id));
                else
                    command.Parameters.Add(Helper.CreateIntParameter("@Phase", ParameterDirection.Input, false, DBNull.Value));
                
                if (!string.IsNullOrEmpty(transformer.UtilityNumber))
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@UtilityNumber", 100, ParameterDirection.Input, false, transformer.UtilityNumber));
                else
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@UtilityNumber", 100, ParameterDirection.Input, false, DBNull.Value));

                if (!string.IsNullOrEmpty(transformer.Manufacturer))
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@Manufacturer", 100, ParameterDirection.Input, false, transformer.Manufacturer));
                else
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@Manufacturer", 100, ParameterDirection.Input, false, DBNull.Value));

                if (!string.IsNullOrEmpty(transformer.Primary))
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@Primary", 100, ParameterDirection.Input, false, transformer.Primary));
                else
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@Primary", 100, ParameterDirection.Input, false, DBNull.Value));
                
                if (!string.IsNullOrEmpty(transformer.Secondary))
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@Secondary", 100, ParameterDirection.Input, false, transformer.Secondary));
                else
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@Secondary", 100, ParameterDirection.Input, false, DBNull.Value));

                command.Parameters.Add(Helper.CreateBitParameter("@Arrestor", ParameterDirection.Input, false, transformer.Arrestor));
                command.Parameters.Add(Helper.CreateBitParameter("@DualRated", ParameterDirection.Input, false, transformer.DualRated));
                command.Parameters.Add(Helper.CreateBitParameter("@Pre1978", ParameterDirection.Input, false, transformer.Pre1978));

                if (transformer.PCBType != null && transformer.PCBType.Id > 0)
                    command.Parameters.Add(Helper.CreateIntParameter("@PCBType", ParameterDirection.Input, false, transformer.PCBType.Id));
                else
                    command.Parameters.Add(Helper.CreateIntParameter("@PCBType", ParameterDirection.Input, false, DBNull.Value));

                if (!string.IsNullOrEmpty(transformer.PPM))
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@PPM", 100, ParameterDirection.Input, false, transformer.PPM));
                else
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@PPM", 100, ParameterDirection.Input, false, DBNull.Value));

                if (transformer.ArrestorKv != null && transformer.ArrestorKv.Id > 0)
                    command.Parameters.Add(Helper.CreateIntParameter("@ArrestorKv", ParameterDirection.Input, false, transformer.ArrestorKv.Id));
                else
                    command.Parameters.Add(Helper.CreateIntParameter("@ArrestorKv", ParameterDirection.Input, false, DBNull.Value));

                command.Parameters.Add(Helper.CreateBitParameter("@CutOut", ParameterDirection.Input, false, transformer.Cutout));

                if (transformer.CutoutKv != null && transformer.CutoutKv.Id > 0)
                    command.Parameters.Add(Helper.CreateIntParameter("@CutoutKv", ParameterDirection.Input, false, transformer.CutoutKv.Id));
                else
                    command.Parameters.Add(Helper.CreateIntParameter("@CutoutKv", ParameterDirection.Input, false, DBNull.Value));

                if (!string.IsNullOrEmpty(transformer.PoleNumber))
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@PoleNumber", 100, ParameterDirection.Input, false, transformer.PoleNumber));
                else
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@PoleNumber", 100, ParameterDirection.Input, false, DBNull.Value));

                if (transformer.Can != null && transformer.Can.Id > 0)
                    command.Parameters.Add(Helper.CreateIntParameter("@Can", ParameterDirection.Input, false, transformer.Can.Id));
                else
                    command.Parameters.Add(Helper.CreateIntParameter("@Can", ParameterDirection.Input, false, DBNull.Value));

                if (transformer.DeterminedBy != null && transformer.DeterminedBy.Id > 0)
                    command.Parameters.Add(Helper.CreateIntParameter("@DeterminedBy", ParameterDirection.Input, false, transformer.DeterminedBy.Id));
                else
                    command.Parameters.Add(Helper.CreateIntParameter("@DeterminedBy", ParameterDirection.Input, false, DBNull.Value));

                if (!string.IsNullOrEmpty(transformer.TestNumber))
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@TestNumber", 100, ParameterDirection.Input, false, transformer.TestNumber));
                else
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@TestNumber", 100, ParameterDirection.Input, false, DBNull.Value));

                if (!string.IsNullOrEmpty(transformer.Impeddance))
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@Impeddance", 100, ParameterDirection.Input, false, transformer.Impeddance));
                else
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@Impeddance", 100, ParameterDirection.Input, false, DBNull.Value));

                if (!string.IsNullOrEmpty(transformer.Taps))
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@Taps", 100, ParameterDirection.Input, false, transformer.Taps));
                else
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@Taps", 100, ParameterDirection.Input, false, DBNull.Value));

                if (!string.IsNullOrEmpty(transformer.Polarity))
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@Polarity", 100, ParameterDirection.Input, false, transformer.Polarity));
                else
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@Polarity", 100, ParameterDirection.Input, false, DBNull.Value));

                if (transformer.ManufDate != DateTime.MinValue)
                    command.Parameters.Add(Helper.CreateDateTimeParameter("@ManufDate", ParameterDirection.Input, false, transformer.ManufDate));
                else
                    command.Parameters.Add(Helper.CreateDateTimeParameter("@ManufDate", ParameterDirection.Input, false, DBNull.Value));

                command.Parameters.Add(Helper.CreateBitParameter("@Sticker", ParameterDirection.Input, false, transformer.Sticker));

                command.ExecuteNonQuery();


                //Gets the last inserted PoleId 
                query = new StringBuilder();
                query.AppendLine("SELECT MAX(TransformerId) AS id FROM tb_Transformers");
                
                command = new SqlCeCommand(query.ToString(), (SqlCeConnection)tx.Connection, tx);
                command.CommandType = CommandType.Text;
                Int32 lastId = (int)command.ExecuteScalar();

                transformer.Id = lastId;

                #endregion

                #region - Image INSERT -

                int imagesCount = 0;

                foreach (EImage image in transformer.Images)
                {
                    query = new StringBuilder("INSERT INTO tb_TransformerImages (idTransformer, FileName) VALUES (");
                    query.AppendLine("@idTransformer, @FileName)");

                    image.GenerateNewName(lastId.ToString() + imagesCount);

                    command = new SqlCeCommand(query.ToString(), con, tx);
                    command.CommandType = CommandType.Text;
                    command.Parameters.Add(Helper.CreateIntParameter("@idTransformer", ParameterDirection.Input, false, lastId));
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@FileName", 150, ParameterDirection.Input, false, image.FileName));
                    command.ExecuteNonQuery();

                    imagesCount++;
                }

                #endregion

                #region - Notes INSERT -

                foreach (ETransformerLookUp note in transformer.Notes)
                {
                    query = new StringBuilder("INSERT INTO tb_Transformers_Keywords (idTransformer, idKeyword) VALUES (");
                    query.AppendLine("@idTransformer, @idKeyword)");

                    command = new SqlCeCommand(query.ToString(), con, tx);
                    command.CommandType = CommandType.Text;
                    command.Parameters.Add(Helper.CreateIntParameter("@idTransformer", ParameterDirection.Input, false, lastId));
                    command.Parameters.Add(Helper.CreateIntParameter("@idKeyword", ParameterDirection.Input, false, note.Id));

                    command.ExecuteNonQuery();
                }

                #endregion
                tx.Commit();
                
                
                return transformer;
            }
            catch (Exception exc)
            {
                if (tx != null)
                    tx.Rollback();

                throw;
            }
            finally
            {
                if (con != null)
                    con.Close();
            }
        }

        public static ETransformer Update(ETransformer transformer)
        {
            SqlCeTransaction tx = null;
            SqlCeConnection con = null;
            StringBuilder query = new StringBuilder();

            try
            {
                con = Helper.GetConnection();
                tx = con.BeginTransaction();

                #region - Transformer UPDATE -

                query.AppendLine("UPDATE tb_Transformers SET ");
                query.AppendLine("Latitude = @Latitude,");
                query.AppendLine("Longitude = @Longitude,");
                query.AppendLine("PoleFk = @PoleFk,");
                query.AppendLine("SerialNumber = @Number,");
                query.AppendLine("CreatedDate = @CreatedDate,");
                query.AppendLine("Comments = @Comments, ");

                query.AppendLine("TransformerType = @TransformerType,");
                query.AppendLine("Type = @Type,");
                query.AppendLine("Class = @Class,");
                query.AppendLine("Phase = @Phase,");
                query.AppendLine("UtilityNumber = @UtilityNumber,");
                query.AppendLine("Manufacturer = @Manufacturer,");
                query.AppendLine("[Primary] = @Primary, ");
                query.AppendLine("Secondary = @Secondary,");
                query.AppendLine("Arrestor = @Arrestor,");
                query.AppendLine("DualRated = @DualRated,");
                query.AppendLine("Pre1978 = @Pre1978,");
                query.AppendLine("PCBType = @PCBType,");
                query.AppendLine("PPM = @PPM, ");
                query.AppendLine("ArrestorKv = @ArrestorKv,");
                query.AppendLine("CutOut = @CutOut ,");
                query.AppendLine("CutOutKv = @CutOutKv,");
                query.AppendLine("PoleNumber = @PoleNumber, ");
                query.AppendLine("Can = @Can, ");
                query.AppendLine("DeterminedBy = @DeterminedBy,");
                query.AppendLine("TestNumber = @TestNumber,");
                query.AppendLine("Impeddance = @Impeddance,");
                query.AppendLine("Taps = @Taps,");
                query.AppendLine("Polarity = @Polarity,");
                query.AppendLine("ManufDate = @ManufDate,");
                query.AppendLine("Sticker = @Sticker");
                query.AppendLine("WHERE TransformerId = @TransformerId");

                SqlCeCommand command = new SqlCeCommand(query.ToString(), con, tx);
                command.CommandType = CommandType.Text;

                command.Parameters.Add(Helper.CreateIntParameter("@TransformerId", ParameterDirection.Input, false, transformer.Id));

                if (transformer.Latitude != Double.MinValue)
                    command.Parameters.Add(Helper.CreateDecimalParameter("@Latitude", ParameterDirection.Input, false, 19, 6, transformer.Latitude));
                else
                    command.Parameters.Add(Helper.CreateDecimalParameter("@Latitude", ParameterDirection.Input, false, 19, 6, DBNull.Value));

                if (transformer.Longitude != Double.MinValue)
                    command.Parameters.Add(Helper.CreateDecimalParameter("@Longitude", ParameterDirection.Input, false, 19, 6, transformer.Longitude));
                else
                    command.Parameters.Add(Helper.CreateDecimalParameter("@Longitude", ParameterDirection.Input, false, 19, 6, DBNull.Value));

                command.Parameters.Add(Helper.CreateIntParameter("@PoleFk", ParameterDirection.Input, false, transformer.Pole.Id));

                if (transformer.SerialNumber != string.Empty)
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@Number", 25, ParameterDirection.Input, false, transformer.SerialNumber));
                else
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@Number", 25, ParameterDirection.Input, false, DBNull.Value));

                if (transformer.CreatedDate != DateTime.MinValue)
                    command.Parameters.Add(Helper.CreateDateTimeParameter("@CreatedDate", ParameterDirection.Input, false, transformer.CreatedDate));
                else
                    command.Parameters.Add(Helper.CreateDateTimeParameter("@CreatedDate", ParameterDirection.Input, false, DBNull.Value));

                if (transformer.Comments != string.Empty)
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@Comments", 512, ParameterDirection.Input, false, transformer.Comments));
                else
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@Comments", 512, ParameterDirection.Input, false, DBNull.Value));

                if (transformer.TransformerType != null && transformer.TransformerType.Id > 0)
                    command.Parameters.Add(Helper.CreateIntParameter("@TransformerType", ParameterDirection.Input, false, transformer.TransformerType.Id));
                else
                    command.Parameters.Add(Helper.CreateIntParameter("@TransformerType", ParameterDirection.Input, false, DBNull.Value));

                if (transformer.Type != null && transformer.Type.Id > 0)
                    command.Parameters.Add(Helper.CreateIntParameter("@Type", ParameterDirection.Input, false, transformer.Type.Id));
                else
                    command.Parameters.Add(Helper.CreateIntParameter("@Type", ParameterDirection.Input, false, DBNull.Value));

                if (transformer.Class != null && transformer.Class.Id > 0)
                    command.Parameters.Add(Helper.CreateIntParameter("@Class", ParameterDirection.Input, false, transformer.Class.Id));
                else
                    command.Parameters.Add(Helper.CreateIntParameter("@Class", ParameterDirection.Input, false, DBNull.Value));

                if (transformer.Phase != null && transformer.Phase.Id > 0)
                    command.Parameters.Add(Helper.CreateIntParameter("@Phase", ParameterDirection.Input, false, transformer.Phase.Id));
                else
                    command.Parameters.Add(Helper.CreateIntParameter("@Phase", ParameterDirection.Input, false, DBNull.Value));

                if (!string.IsNullOrEmpty(transformer.UtilityNumber))
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@UtilityNumber", 100, ParameterDirection.Input, false, transformer.UtilityNumber));
                else
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@UtilityNumber", 100, ParameterDirection.Input, false, DBNull.Value));

                if (!string.IsNullOrEmpty(transformer.Manufacturer))
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@Manufacturer", 100, ParameterDirection.Input, false, transformer.Manufacturer));
                else
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@Manufacturer", 100, ParameterDirection.Input, false, DBNull.Value));

                if (!string.IsNullOrEmpty(transformer.Primary))
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@Primary", 100, ParameterDirection.Input, false, transformer.Primary));
                else
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@Primary", 100, ParameterDirection.Input, false, DBNull.Value));

                if (!string.IsNullOrEmpty(transformer.Secondary))
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@Secondary", 100, ParameterDirection.Input, false, transformer.Secondary));
                else
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@Secondary", 100, ParameterDirection.Input, false, DBNull.Value));

                command.Parameters.Add(Helper.CreateBitParameter("@Arrestor", ParameterDirection.Input, false, transformer.Arrestor));
                command.Parameters.Add(Helper.CreateBitParameter("@DualRated", ParameterDirection.Input, false, transformer.DualRated));
                command.Parameters.Add(Helper.CreateBitParameter("@Pre1978", ParameterDirection.Input, false, transformer.Pre1978));

                if (transformer.PCBType != null && transformer.PCBType.Id > 0)
                    command.Parameters.Add(Helper.CreateIntParameter("@PCBType", ParameterDirection.Input, false, transformer.PCBType.Id));
                else
                    command.Parameters.Add(Helper.CreateIntParameter("@PCBType", ParameterDirection.Input, false, DBNull.Value));

                if (!string.IsNullOrEmpty(transformer.PPM))
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@PPM", 100, ParameterDirection.Input, false, transformer.PPM));
                else
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@PPM", 100, ParameterDirection.Input, false, DBNull.Value));

                if (transformer.ArrestorKv != null && transformer.ArrestorKv.Id > 0)
                    command.Parameters.Add(Helper.CreateIntParameter("@ArrestorKv", ParameterDirection.Input, false, transformer.ArrestorKv.Id));
                else
                    command.Parameters.Add(Helper.CreateIntParameter("@ArrestorKv", ParameterDirection.Input, false, DBNull.Value));

                command.Parameters.Add(Helper.CreateBitParameter("@CutOut", ParameterDirection.Input, false, transformer.Cutout));

                if (transformer.CutoutKv != null && transformer.CutoutKv.Id > 0)
                    command.Parameters.Add(Helper.CreateIntParameter("@CutoutKv", ParameterDirection.Input, false, transformer.CutoutKv.Id));
                else
                    command.Parameters.Add(Helper.CreateIntParameter("@CutoutKv", ParameterDirection.Input, false, DBNull.Value));

                if (!string.IsNullOrEmpty(transformer.PoleNumber))
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@PoleNumber", 100, ParameterDirection.Input, false, transformer.PoleNumber));
                else
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@PoleNumber", 100, ParameterDirection.Input, false, DBNull.Value));

                if (transformer.Can != null && transformer.Can.Id > 0)
                    command.Parameters.Add(Helper.CreateIntParameter("@Can", ParameterDirection.Input, false, transformer.Can.Id));
                else
                    command.Parameters.Add(Helper.CreateIntParameter("@Can", ParameterDirection.Input, false, DBNull.Value));

                if (transformer.DeterminedBy != null && transformer.DeterminedBy.Id > 0)
                    command.Parameters.Add(Helper.CreateIntParameter("@DeterminedBy", ParameterDirection.Input, false, transformer.DeterminedBy.Id));
                else
                    command.Parameters.Add(Helper.CreateIntParameter("@DeterminedBy", ParameterDirection.Input, false, DBNull.Value));

                if (!string.IsNullOrEmpty(transformer.TestNumber))
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@TestNumber", 100, ParameterDirection.Input, false, transformer.TestNumber));
                else
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@TestNumber", 100, ParameterDirection.Input, false, DBNull.Value));

                if (!string.IsNullOrEmpty(transformer.Impeddance))
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@Impeddance", 100, ParameterDirection.Input, false, transformer.Impeddance));
                else
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@Impeddance", 100, ParameterDirection.Input, false, DBNull.Value));

                if (!string.IsNullOrEmpty(transformer.Taps))
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@Taps", 100, ParameterDirection.Input, false, transformer.Taps));
                else
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@Taps", 100, ParameterDirection.Input, false, DBNull.Value));

                if (!string.IsNullOrEmpty(transformer.Polarity))
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@Polarity", 100, ParameterDirection.Input, false, transformer.Polarity));
                else
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@Polarity", 100, ParameterDirection.Input, false, DBNull.Value));

                if (transformer.ManufDate != DateTime.MinValue)
                    command.Parameters.Add(Helper.CreateDateTimeParameter("@ManufDate", ParameterDirection.Input, false, transformer.ManufDate));
                else
                    command.Parameters.Add(Helper.CreateDateTimeParameter("@ManufDate", ParameterDirection.Input, false, DBNull.Value));

                command.Parameters.Add(Helper.CreateBitParameter("@Sticker", ParameterDirection.Input, false, transformer.Sticker));

                command.ExecuteNonQuery();

                #endregion


                #region - Image INSERT -

                query = new StringBuilder("DELETE tb_TransformerImages WHERE idTransformer = @idTransformer");
                command = new SqlCeCommand(query.ToString(), con, tx);
                command.CommandType = CommandType.Text;
                command.Parameters.Add(Helper.CreateIntParameter("@idTransformer", ParameterDirection.Input, false, transformer.Id));
                command.ExecuteNonQuery();

                int imagesCount = 0;

                foreach (EImage image in transformer.Images)
                {
                    query = new StringBuilder("INSERT INTO tb_TransformerImages (idTransformer, FileName) VALUES (");
                    query.AppendLine("@idTransformer, @FileName)");

                    image.GenerateNewName(transformer.Id.ToString() + imagesCount);

                    command = new SqlCeCommand(query.ToString(), con, tx);
                    command.CommandType = CommandType.Text;
                    command.Parameters.Add(Helper.CreateIntParameter("@idTransformer", ParameterDirection.Input, false, transformer.Id));
                    command.Parameters.Add(Helper.CreateNVarCharParameter("@FileName", 150, ParameterDirection.Input, false, image.FileName));
                    command.ExecuteNonQuery();

                    imagesCount++;
                }

                #endregion

                #region - Notes INSERT -

                query = new StringBuilder("DELETE tb_Transformers_Keywords WHERE idTransformer = @idTransformer");
                command = new SqlCeCommand(query.ToString(), con, tx);
                command.CommandType = CommandType.Text;
                command.Parameters.Add(Helper.CreateIntParameter("@idTransformer", ParameterDirection.Input, false, transformer.Id));
                command.ExecuteNonQuery();

                foreach (ETransformerLookUp note in transformer.Notes)
                {
                    query = new StringBuilder("INSERT INTO tb_Transformers_Keywords (idTransformer, idKeyword) VALUES (");
                    query.AppendLine("@idTransformer, @idKeyword)");

                    command = new SqlCeCommand(query.ToString(), con, tx);
                    command.CommandType = CommandType.Text;
                    command.Parameters.Add(Helper.CreateIntParameter("@idTransformer", ParameterDirection.Input, false, transformer.Id));
                    command.Parameters.Add(Helper.CreateIntParameter("@idKeyword", ParameterDirection.Input, false, note.Id));

                    command.ExecuteNonQuery();
                }

                #endregion

                tx.Commit();

                return transformer;
            }
            catch
            {
                if (tx != null)
                    tx.Rollback();

                throw;
            }
            finally
            {
                if (con != null)
                    con.Close();
            }
        }

        public static void Delete(ETransformer transformer)
        {
            SqlCeTransaction tx = null;
            SqlCeConnection con = null;
            StringBuilder query = new StringBuilder();

            try
            {
                con = Helper.GetConnection();
                tx = con.BeginTransaction();

                #region - Trasnsformer DELETE -

                query.AppendLine("DELETE tb_Transformers ");
                query.AppendLine("WHERE TransformerId = @TransformerId ");

                SqlCeCommand command = new SqlCeCommand(query.ToString(), con, tx);
                command.CommandType = CommandType.Text;
                command.Parameters.Add(Helper.CreateIntParameter("@TransformerId", ParameterDirection.Input, false, transformer.Id));
                command.ExecuteNonQuery();

                query = new StringBuilder("DELETE tb_TransformerImages WHERE idTransformer = @idTransformer");
                command = new SqlCeCommand(query.ToString(), con, tx);
                command.CommandType = CommandType.Text;
                command.Parameters.Add(Helper.CreateIntParameter("@idTransformer", ParameterDirection.Input, false, transformer.Id));
                command.ExecuteNonQuery();

                query = new StringBuilder("DELETE tb_Transformers_Keywords WHERE idTransformer = @idTransformer");
                command = new SqlCeCommand(query.ToString(), con, tx);
                command.CommandType = CommandType.Text;
                command.Parameters.Add(Helper.CreateIntParameter("@idTransformer", ParameterDirection.Input, false, transformer.Id));
                command.ExecuteNonQuery();

                #endregion

                tx.Commit();
            }
            catch
            {
                if (tx != null)
                    tx.Rollback();

                throw;
            }
            finally
            {
                if (con != null)
                    con.Close();
            }
        }

        public static void Delete(List<ETransformer> transformers)
        {
            SqlCeTransaction tx = null;
            SqlCeConnection con = null;
            StringBuilder query = new StringBuilder();

            try
            {
                con = Helper.GetConnection();
                tx = con.BeginTransaction();

                foreach (ETransformer transformer in transformers)
                {
                    #region - Meter DELETE -

                    query.AppendLine("DELETE tb_Transformers ");
                    query.AppendLine("WHERE TransformerId = @TransformerId ");

                    SqlCeCommand command = new SqlCeCommand(query.ToString(), con, tx);
                    command.CommandType = CommandType.Text;

                    command.Parameters.Add(Helper.CreateIntParameter("@TransformerId", ParameterDirection.Input, false, transformer.Id));

                    command.ExecuteNonQuery();

                    #endregion
                }

                tx.Commit();
            }
            catch
            {
                if (tx != null)
                    tx.Rollback();

                throw;
            }
            finally
            {
                if (con != null)
                    con.Close();
            }
        }

        public static List<ETransformer> GetAll(EPole pole)
        {
            SqlCeConnection con = null;
            StringBuilder query = new StringBuilder();

            try
            {
                con = Helper.GetConnection();

                query.Append("SELECT TransformerId, Latitude, Longitude, SerialNumber, CreatedDate, Comments, TransformerType, Type, Class, Phase, UtilityNumber, Manufacturer, [Primary], Secondary, Arrestor, DualRated, Pre1978, PCBType, PPM ");
                query.Append("FROM tb_Transformers ");
                query.Append("WHERE PoleFk = @PoleId ");

                SqlCeCommand command = new SqlCeCommand(query.ToString(), con);
                command.CommandType = CommandType.Text;
                command.Parameters.Add(Helper.CreateIntParameter("@PoleId", ParameterDirection.Input, false, pole.Id));

                SqlCeDataReader dr = command.ExecuteReader();
                List<ETransformer> transformers = new List<ETransformer>();

                while (dr.Read())
                {
                    ETransformer loadedTransformer = new ETransformer();

                    loadedTransformer.Id = dr.GetInt32(dr.GetOrdinal("TransformerId"));

                    if (!dr.IsDBNull(dr.GetOrdinal("Latitude")))
                        loadedTransformer.Latitude = dr.GetDouble(dr.GetOrdinal("Latitude"));

                    if (!dr.IsDBNull(dr.GetOrdinal("Longitude")))
                        loadedTransformer.Longitude = dr.GetDouble(dr.GetOrdinal("Longitude"));

                    if (!dr.IsDBNull(dr.GetOrdinal("SerialNumber")))
                        loadedTransformer.SerialNumber = dr.GetString(dr.GetOrdinal("SerialNumber"));

                    if (!dr.IsDBNull(dr.GetOrdinal("CreatedDate")))
                        loadedTransformer.CreatedDate = dr.GetDateTime(dr.GetOrdinal("CreatedDate"));

                    if (!dr.IsDBNull(dr.GetOrdinal("Comments")))
                        loadedTransformer.Comments = dr.GetString(dr.GetOrdinal("Comments"));

                    if (!dr.IsDBNull(dr.GetOrdinal("TransformerType")))
                        loadedTransformer.TransformerType = DTransformerLookup.GetByIdType(dr.GetInt32(dr.GetOrdinal("TransformerType")), TransformerLookUpType.TransformerType);

                    if (!dr.IsDBNull(dr.GetOrdinal("Class")))
                        loadedTransformer.Class = DTransformerLookup.GetByIdType(dr.GetInt32(dr.GetOrdinal("Class")), TransformerLookUpType.Class);

                    if (!dr.IsDBNull(dr.GetOrdinal("Phase")))
                    loadedTransformer.Phase = DTransformerLookup.GetByIdType(dr.GetInt32(dr.GetOrdinal("Phase")), TransformerLookUpType.Phase);

                    if (!dr.IsDBNull(dr.GetOrdinal("Type")))
                        loadedTransformer.Type = DTransformerLookup.GetByIdType(dr.GetInt32(dr.GetOrdinal("Type")), TransformerLookUpType.Type);

                    if (!dr.IsDBNull(dr.GetOrdinal("PCBType")))
                        loadedTransformer.PCBType = DTransformerLookup.GetByIdType(dr.GetInt32(dr.GetOrdinal("PCBType")), TransformerLookUpType.PCBType);

                    if (!dr.IsDBNull(dr.GetOrdinal("UtilityNumber")))
                        loadedTransformer.UtilityNumber = dr.GetString(dr.GetOrdinal("UtilityNumber"));

                    if (!dr.IsDBNull(dr.GetOrdinal("Manufacturer")))
                        loadedTransformer.Manufacturer = dr.GetString(dr.GetOrdinal("Manufacturer"));

                    if (!dr.IsDBNull(dr.GetOrdinal("Primary")))
                        loadedTransformer.Primary = dr.GetString(dr.GetOrdinal("Primary"));

                    if (!dr.IsDBNull(dr.GetOrdinal("Secondary")))
                        loadedTransformer.Secondary = dr.GetString(dr.GetOrdinal("Secondary"));

                    if (!dr.IsDBNull(dr.GetOrdinal("Arrestor")))
                        loadedTransformer.Arrestor = dr.GetBoolean(dr.GetOrdinal("Arrestor"));

                    if (!dr.IsDBNull(dr.GetOrdinal("DualRated")))
                        loadedTransformer.DualRated = dr.GetBoolean(dr.GetOrdinal("DualRated"));

                    if (!dr.IsDBNull(dr.GetOrdinal("Pre1978")))
                        loadedTransformer.Pre1978 = dr.GetBoolean(dr.GetOrdinal("Pre1978"));

                    if (!dr.IsDBNull(dr.GetOrdinal("PPM")))
                        loadedTransformer.PPM = dr.GetString(dr.GetOrdinal("PPM"));

                    transformers.Add(loadedTransformer);
                }

                return transformers;
            }
            finally
            {
                if (con != null)
                    con.Close();
            }
        }

        #endregion
    }
}
